#!/usr/bin/python
# -*- coding: UTF-8 -*-
__author__ = 'dengqianyi'

#os lib
import os
import openpyxl
from openpyxl.reader.excel import load_workbook
from xml.etree.ElementTree import ElementTree
from xml.etree.ElementTree import Element
from xml.etree.ElementTree import SubElement
#our lib
from myExceptions import MyException
from myPrint import MyPrint


SpecialSheetName = "keyvalue"
SpecialKeyWordsList=list()
# SpecialKeyWordsList.append("&&onlyone&&".decode('utf-8'))
SpecialKeyValue_KEY = "&&key&&".decode("utf-8")
SpecialKeyWordsList.append(SpecialKeyValue_KEY)
SpecialKeyWordsList.append("&&end&&".decode('utf-8'))

# SpecialKeyWordsList.append("&&timeTick&&".decode('utf-8'))
# SpecialKeyWordsList.append("&&empty&&".decode('utf-8'))

def IsSpecialKeyWords(str):
    _result = False
    for value in SpecialKeyWordsList:
        # print (value == str),value,str
        if value == str:
            _result = True
    return _result
    pass
def IsEmptyCell(value):
    if value == None:
        return True
    return False
def IsSpecialValue(str):
    if IsSpecialKeyWords(str) == True or IsEmptyCell(str) == True:
        return True
    return False

class XlsComponent(object):
    def __init__(self,xlsPathName,outputXmlDir=None,outputXmlName = None,isOutFileFlag=False):
        self._xlsPathName = xlsPathName
        if outputXmlDir != None:
            self._outputPathXmlDir = outputXmlDir
        if outputXmlName != None:
            self._outputPathXmlName = outputXmlName
        self._xlsName = os.path.split(self._xlsPathName)[1]
        self._xlsLastChangeTime = None
        self._xlsKeyValueDict = dict()
        self._isOutPutFileFlag = isOutFileFlag

        self.init()
        pass
    def init(self):
        if self._xlsPathName != None:
            self._xlsObject = load_workbook(self._xlsPathName)

            if self._xlsObject == None:
                MyException(self._xlsPathName," can't find excel!")
            #xls内容的重新整理存储
            self._xlsContentDict = dict()
            self._xlsTitleDict = dict()
            self._xlsSpecialKeyWordsDict = dict()
            self._xlsSheetName = self._xlsObject.get_sheet_names()
            for _sheetName in self._xlsSheetName:
                if _sheetName != SpecialSheetName:
                    self._xlsSpecialKeyWordsDict[_sheetName] = dict()#parseSheet中会解析赋值
                    self._xlsTitleDict[_sheetName] = self._parseSheetTitle(self._xlsObject,_sheetName)
                    self._xlsContentDict[_sheetName] = self._parseSheet(self._xlsObject,_sheetName)
                pass
            # self._xlsMD5Value = self.caculateFileMD5()
            self._xlsKeyValue()
            self._outputPathDir = os.path.split(self._xlsPathName)[0]
            self._xlsLastChangeTime = os.stat(self._xlsPathName).st_ctime
            pass
        pass
    def _parseSheetTitle(self,xlsObj,sheetName):
        _rowTitleContent = list()
        _sheetObj = xlsObj.get_sheet_by_name(sheetName)
        for _column in xrange(1,_sheetObj.get_highest_column()+1):
            _cell = _sheetObj.cell(row=1,column=_column)
            _rowTitleContent.append(_cell.value)
        return _rowTitleContent
        pass
    def _parseSheet(self,xlsObj,sheetName):
        """
        对表的当前内容进行dict,除却第一行和最后一行
        解析表，返回一个内容的list[{row1ContentDict},{row2ContentDict},{rowNumContentDict}......]
        rowContentDict ={"标题1":内容1,"标题2":内容2,......}
        当格子的内容为空时，赋值为None
        如果最后一行为特殊字符串，不写入到dict中
        """
        sheetObj = xlsObj.get_sheet_by_name(sheetName)
        _max_rows = sheetObj.get_highest_row()
        _max_columns = sheetObj.get_highest_column()
        _sheetContentList = list()
        if self._isOutPutFileFlag == True:
            #是输出文件的话，不需要做行数判断
            return _sheetContentList
        _sheetHeadLineList = self._xlsTitleDict[sheetName]
        if _sheetHeadLineList == None:
            MyException(sheetName,"sheet not have title row")
            return
        if _max_rows == 1:
            MyException("表格不能为空".decode('utf-8'),sheetName)
        if _max_rows == 2:
            #只有表头，表尾，没有表内容
            return _sheetContentList
        #除去标题行的每行内容
        for rowNum in xrange(2,_max_rows+1):
            # 得到某一行的数据，组织成dict
            _rowContent = dict()
            for columnNum in xrange(1,len(_sheetHeadLineList)+1):
                _cell = sheetObj.cell(row=rowNum,column=columnNum)
                # MyPrint("parseSheet",columnNum,_max_columns,len(_sheetHeadLineList),columnNum-1)
                # print _sheetHeadLineList
                _head = _sheetHeadLineList[columnNum - 1]
                # 对excel的伪整形的处理
                if type(_cell.value) == type(1):
                    _rowContent[_head] = str(_cell.value)
                elif type(_cell.value) == type(0.0):
                    _strValue = str(_cell.value)
                    _strPointValue = _strValue.split(".")[1]
                    if _strPointValue == '0':
                        _rowContent[_head] = _strValue.split('.')[0]
                    else:
                        _rowContent[_head] = _strValue
                elif IsSpecialKeyWords(_cell.value) == True:
                    #为特殊的标识行了,即最后一行
                    self._xlsSpecialKeyWordsDict[sheetName] = self._parseSheetSpecialKeyWord(self._xlsObject,sheetName,rowNum)
                    return _sheetContentList
                else:
                    _rowContent[_head] = _cell.value
            pass
            _sheetContentList.append(_rowContent)
        return  _sheetContentList
    def saveLastContentToXls(self,defaultName=None,contenDict=None):
        # 把self._xlsContentDict保存成xls
        _outputFile = None
        _contentDict = self._xlsContentDict
        if contenDict != None:
            _contentDict = contenDict
        if defaultName != None:
            _outputFile = os.path.splitext(self._xlsPathName)[0]+defaultName+os.path.splitext(self._xlsName)[1]
        else:
            _outputFile = os.path.splitext(self._xlsPathName)[0]+os.path.splitext(self._xlsName)[1]
        if os.path.isfile(_outputFile) == True:
            os.remove(_outputFile)
        _wb = None
        _wb = openpyxl.Workbook()
        _sheetNames = self._xlsObject.get_sheet_names()
        index = 0
        # 创建sheet
        # print "SaveLastContentToXls",_sheetNames
        for sheetname in _sheetNames:
            _wb.create_sheet(index,sheetname)
            index = index + 1
        # 删除创建时产生的'sheet'表
        _wb.remove_sheet(_wb.get_sheet_by_name('Sheet'))
        # print "SaveLastContentToXls 1",_wb.get_sheet_names()
        # 填写sheet
        for _sheetName,_sheetContentList in _contentDict.items():
            if _sheetName != SpecialSheetName:
                _sheetObject = _wb.get_sheet_by_name(_sheetName)
                if _sheetObject:
                    #写标题栏
                    _headLineList = self._xlsTitleDict[_sheetName]
                    for index in xrange(0,len(_headLineList)):
                        _sheetObject.cell(row = 1,column=index+1).value = _headLineList[index]
                    #写内容栏
                    _max_rows = 2
                    for index in xrange(0,len(_sheetContentList)):
                        _rowContentDict = _sheetContentList[index]
                        for _columnName,_value in _rowContentDict.items():
                            _columnNum = self._getColumnByContent(_columnName,_headLineList)
                            _sheetObject.cell(row=_max_rows,column=_columnNum).value=_value
                        _max_rows = _max_rows + 1
                else:
                    raise IOError,("sheet content empty","function--->_writeToBook")
                    pass
        if _wb.get_sheet_by_name(SpecialSheetName) != None:
            _sheetObject = _wb.get_sheet_by_name(SpecialSheetName)
            _sheetObject.cell(row=1,column=1).value='key'
            _sheetObject.cell(row=1,column=2).value='value'
            index = 2
            for key,value in self._xlsKeyValueDict.items():
                _sheetObject.cell(row=index,column=1).value=key
                _sheetObject.cell(row=index,column=2).value=value
                index += 1
                pass
        print "SaveLastContentToXls _outputFile",_outputFile
        _wb.save(_outputFile)
        pass
    def _xlsKeyValue(self):
        self._xlsKeyValueDict = dict()
        _xls = self._xlsObject
        _keyvalue_sheet = self._xlsObject.get_sheet_by_name(SpecialSheetName)
        if _keyvalue_sheet == None or _keyvalue_sheet.get_highest_row() == 1:
            return
        _max_rows = _keyvalue_sheet.get_highest_row()
        for _index in xrange(2,_max_rows+1):
            _keyCell = _keyvalue_sheet.cell(row=_index,column=1).value
            _valueCell = _keyvalue_sheet.cell(row=_index,column=2).value
            if IsSpecialValue(_keyCell) != True and IsSpecialValue(_valueCell) != True:
                self._xlsKeyValueDict[_keyCell] = _valueCell
        pass
    def getXlsPathDir(self):
        return self._xlsPathName
    def getXlsContentDict(self):
        return self._xlsContentDict
    def getTitleDict(self):
        return self._xlsTitleDict
    def getXlsSpecialKeyDict(self):
        return self._xlsKeyValueDict
    def getSheetSpecialKeyDict(self,sheetName):
        return self._xlsSpecialKeyWordsDict.get(sheetName)
    def _parseSheetSpecialKeyWord(self,xlsObj,sheetName,rowNum):
        _rowSpecialKeyWord = dict()
        _sheetObj = xlsObj.get_sheet_by_name(sheetName)
        _max_rows = rowNum
        #存储rowNum行的特殊字符
        for _index in xrange(1,len(self._xlsTitleDict[sheetName])+1):
            _cell = _sheetObj.cell(row=_max_rows,column=_index)
            if IsSpecialKeyWords(_cell.value) == True:
                _rowSpecialKeyWord[self._xlsTitleDict[sheetName][_index - 1]] = _cell.value
        return _rowSpecialKeyWord
        pass
    def _getColumnByContent(self,text,topicList):
        # print 'getColumnByContent',text,topicList
        for _index in xrange(0,len(topicList)):
            if topicList[_index] == text:
                return _index+1
            pass
        print text,topicList
        raise IOError,("Errors Happen","getColumnByContent")
        pass
    def replaceKeyByValue(self,contentList,sheetSpecialKeyWordsDict,keyvalueDict):
        #把某一列的值变成keyvalue表里对应的value值
        #sheetSpecialKeyWordsDict == {"title":"&&key&&"}
        #找到尾行为 &&key&& 的那一列
        for rowContentDict in contentList:
            for key,value in rowContentDict.items():
                _lastValue = sheetSpecialKeyWordsDict.get(key)
                if _lastValue == SpecialKeyValue_KEY:
                    if keyvalueDict.get(value) != None:
                        rowContentDict[key] = keyvalueDict.get(value)
                    pass
        pass
    def saveContentToXmlFile(self,contentList,xmlfilepath,sheetName):
        def __indent(elem, level=0):
            i = "\n" + level*"  "
            if len(elem):
                if not elem.text or not elem.text.strip():
                    elem.text = i + "  "
                for e in elem:
                    __indent(e, level+1)
                if not e.tail or not e.tail.strip():
                    e.tail = i
            if level and (not elem.tail or not elem.tail.strip()):
                elem.tail = i
            return elem
        if contentList == None or xmlfilepath == None:
            return
        # 有的话删除
        if os.path.isfile(xmlfilepath) == True:
            os.remove(xmlfilepath)
        _tree = ElementTree(element=Element("sgs"))
        _tree.write(file_or_filename=xmlfilepath,encoding='UTF-8')
        _tree = ElementTree(file=xmlfilepath)
        _treeRoot = _tree.getroot()
        for index in xrange(0,len(contentList)):
            _rowContent = contentList[index]
            _item = Element(sheetName)
            for key,value in _rowContent.items():
                if value == None:
                    value = str(0)
                elif type(value) == type(4110030101) or type(value) ==type(100):
                    value = str(value)
                # print "value",value,type(value)
                SubElement(_item,key).text=value
            _treeRoot.append(_item)
        __indent(_treeRoot)
        _tree.write(file_or_filename=xmlfilepath,encoding='UTF-8')
        pass
    pass